from openpyxl import Workbook, load_workbook
from tqdm import tqdm


class MergeFiles:
    def __init__(self, file1, file2):
        self.chandao_changelog = file1
        self.bug_ids = file2
        self.bug_idss = []  # [1,2,3,4,5]
        self.chandao_all_datas = []  # [[id,title],[id2,title2]]
        self.bug_ids_all_datas = []  # [[id,branch,title],[id2,branch2,title2]]
        self.finall_datas = []

    def write_excel(self):
        save_dapth = "最终数据.xlsx"
        wb = Workbook()
        ws = wb.active
        title = ['bug号', '分支', '所属模块', '严重程度','bug标题']
        self.finall_datas.insert(0, title)
        for index, value in enumerate(self.finall_datas):
            for index2, value2 in enumerate(value):
                ws.cell(index + 1, index2 + 1, value=value2)
        wb.save(save_dapth)
        wb.close()
        print("数据合并成功,最终数据保存在：",save_dapth,"最终数据共计{0}条".format(len(self.finall_datas)))

    def read_datas(self):
        print("合并最终数据...")
        wb = load_workbook(self.chandao_changelog)  # 获取禅道changelog数据
        sheet = wb['Sheet']
        max_row = sheet.max_row
        for i in range(max_row - 1):
            tmp_data = []
            bug_id = sheet.cell(i + 2, 1).value  # id/title
            bug_branch = sheet.cell(i + 2, 2).value  # id/title
            bug_modular = sheet.cell(i + 2, 3).value  # id/title
            bug_Severity = sheet.cell(i + 2, 4).value  # id/title
            bug_title = sheet.cell(i + 2, 5).value
            tmp_data.append(int(bug_id))
            tmp_data.append(bug_branch)
            tmp_data.append(bug_modular)
            tmp_data.append(bug_Severity)
            tmp_data.append(bug_title)
            self.chandao_all_datas.append(tmp_data)

        wb2 = load_workbook(self.bug_ids)  # 获取bugids数据
        sheet2 = wb2['Sheet']
        max_row2 = sheet2.max_row
        for i in range(max_row2 - 1):
            tmp_data = []
            bug_id = sheet2.cell(i + 2, 1).value  # id/branch/title
            branch = sheet2.cell(i + 2, 2).value
            modular = sheet2.cell(i + 2, 3).value
            bug_Severity = sheet2.cell(i + 2, 4).value
            bug_title = sheet2.cell(i + 2, 5).value
            tmp_data.append(int(bug_id))
            tmp_data.append(branch)
            tmp_data.append(modular)
            tmp_data.append(bug_Severity)
            tmp_data.append(bug_title)
            self.bug_idss.append(int(bug_id))
            self.bug_ids_all_datas.append(tmp_data)

        # print(self.chandao_all_datas)

    def main(self):
        self.read_datas()
        for i in self.chandao_all_datas:
            tmp_datas = []
            if i[4] == None:  # 这行数据没有标题
                bug_id = i[0]
                try:
                    bug_index = self.bug_idss.index(bug_id)  # 如果bug号不在列表中
                except:
                    self.finall_datas.append(i)
                    continue
                branch = self.bug_ids_all_datas[bug_index][1]
                bug_modular = self.bug_ids_all_datas[bug_index][2]
                bug_Severity = self.bug_ids_all_datas[bug_index][3]
                bug_title = self.bug_ids_all_datas[bug_index][4]
                tmp_datas.append(bug_id)
                tmp_datas.append(branch)
                tmp_datas.append(bug_modular)
                tmp_datas.append(bug_Severity)
                tmp_datas.append(bug_title)
                if tmp_datas in self.finall_datas:
                    continue
                self.finall_datas.append(tmp_datas)
                continue
            if i in self.finall_datas:
                continue
            self.finall_datas.append(i)
        self.write_excel()


if __name__ == '__main__':
    mf = MergeFiles('tmp_datas/禅道-changelog合并数据.xlsx', 'tmp_datas/禅道ID号配置文件.xlsx')
    mf.main()
